package com.bears.god.sup.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.bears.god.sup.model.Income;
import com.bears.god.sup.util.DBConnection;



public class IncomeDAOImp implements IIncomeDAO {
	private static final String FINDBYID="select * from income where id=?";
	private static final String FIND_ALL = "select * from income";
	private static final String FIND_FIVE = "select * from income LIMIT ?,?";
	private static final String INSERT= "insert into income"
			+ "(dept_name,buss_date,last_date,daily_income) "
			+ "values(?,?,?,?)";
	private static final String UPDATE= "update income set "
			+ "last_date=?,daily_income=?"
			+ " where id=?";
	
	
	@Override
	public List<Income> findAll() throws Exception {
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rs = null;

		List<Income> inc = new ArrayList<Income>();

		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(FIND_ALL);
			rs = prep.executeQuery();

			while (rs.next()) {
				Income income = new Income();
				income.setDeptName(rs.getString("dept_name"));
				income.setBussDate(rs.getString("buss_date"));
				income.setId(rs.getInt("id"));
				income.setDailyIncome(rs.getDouble("daily_income"));
				income.setLastDate(rs.getString("last_date"));

				inc.add(income);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, prep, conn);
		}
		return inc;
	}

	@Override
	public List<Income> findByfiveId(int num, int endnum) throws Exception {
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rs = null;

		List<Income> inc = new ArrayList<Income>();

		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(FIND_FIVE);
			prep.setInt(1, num);
			prep.setInt(2, endnum);
			rs = prep.executeQuery();

			while (rs.next()) {
				Income income = new Income();
				income.setDeptName(rs.getString("dept_name"));
				income.setBussDate(rs.getString("buss_date"));
				income.setId(rs.getInt("id"));
				income.setDailyIncome(rs.getDouble("daily_income"));
				income.setLastDate(rs.getString("last_date"));

				inc.add(income);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, prep, conn);
		}
		return inc;
	}

	@Override
	public void save(Income income) throws Exception {
		PreparedStatement prep = null;
		Connection conn =null;
		
		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(INSERT);
			prep.setString(1, income.getDeptName());
			prep.setString(2, income.getBussDate());
			prep.setString(3, income.getLastDate());
			prep.setDouble(4, income.getDailyIncome());
		
			prep.executeUpdate();
			ResultSet rst = prep.getGeneratedKeys();
			rst.next();
			int id = rst.getInt(1);
			income.setId(id);
			
		} finally {
			DBConnection.close(prep, conn);
			}
	}

	@Override
	public void update(Income income) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		
		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(UPDATE);
			prep.setString(1, income.getLastDate());
			prep.setDouble(2, income.getDailyIncome());
			prep.setInt(3, income.getId());
			
			prep.executeUpdate();
		} finally {
			DBConnection.close(prep, conn);
		}
	}

	@Override
	public Income findById(int id) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		ResultSet rst = null;
		Income income = null;
		

		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(FINDBYID);
			prep.setInt(1, id);
			
			rst = prep.executeQuery();
			if(rst.next())
			{
				income = new Income();
				
				income.setDeptName(rst.getString("dept_name"));
				income.setBussDate(rst.getString("buss_date"));
				income.setId(rst.getInt("id"));
				income.setDailyIncome(rst.getDouble("daily_income"));
				income.setLastDate(rst.getString("last_date"));
			}
			
		}
		finally
		{
			DBConnection.close(rst, prep, conn);
		}
		return income;
	}
}
